{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 44,
   "id": "56bb0801",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "c:\\ProgramData\\scoop\\apps\\miniconda3\\24.9.2-0\\Lib\\site-packages\\openpyxl\\styles\\stylesheet.py:237: UserWarning: Workbook contains no default style, apply openpyxl's default\n",
      "  warn(\"Workbook contains no default style, apply openpyxl's default\")\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import re\n",
    "\n",
    "orders_file = r\"C:/users/Administrator/Downloads/2025-06-03 08_49_52-order数据.xlsx\"\n",
    "domain_table = r\"C:/users/Administrator/Downloads/site_records_cxxu.xlsx\"\n",
    "result_file = r\"C:/users/Administrator/Desktop/result.xlsx\"\n",
    "df = pd.read_excel(orders_file)\n",
    "p = re.compile(r\"([-\\w]+\\.){1,2}[-\\w]+\")\n",
    "# df.info()\n",
    "\n",
    "\n",
    "def extract_domain(url):\n",
    "    \"\"\"\n",
    "    # 测试URL列表\n",
    "    urls = ['www.domain.com', 'https://www.dom-ain.com', 'domain-test.com', 'http://domain.com', 'https://domain.com/']\n",
    "    \"\"\"\n",
    "    # 使用正则表达式提取域名\n",
    "    match = re.search(r\"(?:https?://)?(?:www\\.)?([^/]+)\", url)\n",
    "    if match:\n",
    "        return match.group(1)\n",
    "    return None\n",
    "\n",
    "\n",
    "df1 = df[[\"产品名称\", \"域名\"]].copy()\n",
    "df1[\"域名\"] = df1[\"域名\"].apply(extract_domain)\n",
    "df1.drop_duplicates(subset=[\"产品名称\"], inplace=True)\n",
    "# 使用在线表格下载下来的excel表格格式肯能不符标准规范,可以用office excel打开(启用编辑)然后保存(会尝试保存为标准excel格式)\n",
    "df2 = pd.read_excel(domain_table)\n",
    "# df2.info()\n",
    "df2 = df2[[\"域名\", \"国家\"]].copy()\n",
    "df2[\"域名\"] = df2[\"域名\"].apply(extract_domain)\n",
    "\n",
    "\n",
    "# 连接df1和df2,依据为相同的域名\n",
    "df = pd.merge(df1, df2, on=\"域名\", how=\"inner\")\n",
    "df.to_excel(result_file, index=False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}
